from PyQt5.QtWidgets import QApplication, QMainWindow,QWidget,QTableWidgetItem,QMessageBox
from PyQt5.uic import loadUiType
import connect_mysql

#####################################动态加载UI文件，实现UI与逻辑操作分离######################################################
# UI--Logic 分离 动态加载UI文件
dl, _ = loadUiType('ygdl.ui')
zc, _ = loadUiType('ygzc.ui')
ygmain, _ =loadUiType('ygmain.ui')
#####################################注册登录界面###############################################################################
class Ygzc(QWidget,zc):
    def __init__(self):
        QWidget.__init__(self)
        self.setupUi(self)
        self.button()
    def button(self):
        self.pushButton12.clicked.connect(self.fanhui)
        self.pushButtondl_zcuser.clicked.connect(self.zcuser1)

    def fanhui(self):
        self.app=Ygdl()
        self.close()
        self.app.show()

    def zcuser1(self):
        db = con_mysql.get_conn()  # 数据库连接
        cur = db.cursor()  # 创建游标
        sql = "insert into user(name,pwd) values(%s,%s)"  # sql语句
        username = self.lineEdi_username.text()  # 实现从指定界面输入框获取指定输入信息
        userpwd = self.lineEdit_userpwd.text()
        userpwd1 = self.lineEditqd.text()
        if userpwd == userpwd1:  # 如果密码和确认密码相同，则执行以下代码
            cur.execute(sql, (username, userpwd))  # 执行sql语句
            db.commit()  # 提交
            con_mysql.close_conn(cur, db)
        else:
            self.label_4.setText('两次密码不一致')

#####################################################dlu##############################################333###################
class Ygdl(QWidget,dl):
    def __init__(self):
        QWidget.__init__(self)
        self.setupUi(self)
        self.button()

    def button(self):
        self.pushButton_2zc1.clicked.connect(self.zhuce)
        self.pushButtondl.clicked.connect(self.denglu)

    def zhuce(self):
        self.app=Ygzc()
        self.close()
        self.app.show()

    def denglu(self):
        db = con_mysql.get_conn()   #连接数据库
        cur = db.cursor()          #定义游标
        sql = "select * from user where name=%s and pwd=%s"  #sql语句
        a = self.lineEdi_username.text()
        b = self.lineEdit_userpwd.text()
        cur.execute(sql, (a, b))   # 执行sql语句
        data = cur.fetchone()      # 取其中一条数据
        if data:                   #如果存在数据，则执行以下功能
            self.app =Ygmain()   # 把mainAPP窗口赋值为app
            self.close()           #当前窗口关闭
            self.app.show()         #主窗口打开

#####################################################main####################################################################
class Ygmain(QMainWindow,ygmain):
    def __init__(self):
        QMainWindow.__init__(self)
        self.setupUi(self)
        self.button()
        self.jiemiangaibian()
        self.show_yg()
        self.show_yggz()
        self.show_bm()

    def button(self):
        self.pushButton.clicked.connect(self.yginfo_tab)
        self.pushButton_2.clicked.connect(self.yggz_tab)
        self.pushButton_3.clicked.connect(self.bminfo_tab)
        self.pushButton_5.clicked.connect(self.zhanghu_tab)
        self.pushButton_4.clicked.connect(self.fanhui)
        self.pushButton_6.clicked.connect(self.add_yginfo)
        self.pushButton_info.clicked.connect(self.chaxun_yg)
        self.pushButtonss.clicked.connect(self.ssinfo)
        self.pushButton_7.clicked.connect(self.xginfo)
        self.pushButton_8sc.clicked.connect(self.deleteinfo)
        self.pushButton_9.clicked.connect(self.add_yggz)
        self.pushButtonss_2.clicked.connect(self.ssgz)
        self.pushButton_8.clicked.connect(self.xg_gzinfo)
        self.pushButton_8sc_2.clicked.connect(self.delete_gz_info)
        self.pushButton_10.clicked.connect(self.add_bm)
        self.pushButton_cxbminfo_3.clicked.connect(self.chaxun_bm)
        self.pushButton_11.clicked.connect(self.xg_bminfo)
        self.pushButton_12.clicked.connect(self.delete_bm_info)
        self.pushButton_ssinfo_4.clicked.connect(self.ssbm)
        self.pushButtondeng.clicked.connect(self.user_login)
        self.pushButtonxiugai.clicked.connect(self.xg_user)

    def jiemiangaibian(self):
        self.tabWidget.tabBar().setVisible(False)  #隐藏tab 把最上面那个容器隐藏

    def fanhui(self):
        self.app=Ygdl()   #实例变量问题，必须把它作为自己的一个实例变量才可以，不然是属于这样一个方法里面的临时变量，当这个方法结束以后这个变量就销毁了。
        self.close()
        self.app.show()

    def yginfo_tab(self):
        self.tabWidget.setCurrentIndex(0)    #索引为0
    def yggz_tab(self):
        self.tabWidget.setCurrentIndex(1)
    def bminfo_tab(self):
        self.tabWidget.setCurrentIndex(2)    #索引为1
    def zhanghu_tab(self):
        self.tabWidget.setCurrentIndex(3)

    def add_yginfo(self):
        #数据库操作流程
        #1.获取连接
        db = con_mysql.get_conn()
        #2.获取游标
        cur = db.cursor()
        # 3.SQL语句
        sql = "insert into yginfo(id,name,sex,age,bm_id) values(%s,%s,%s,%s,%s)"
        a = self.add_yg_id.text()   #实现从指定界面输入框获取指定输入信息
        b= self.addname.text()
        c= self.addsex.text()
        d = self.addage.text()
        e = self.addbmid.text()

        # 4.执行语句
        cur.execute(sql, (a,b,c,d,e))
        #5.提交
        db.commit()
        #6.关闭资源
        con_mysql.close_conn(cur,db)

    def show_yg(self):
        db = con_mysql.get_conn()
        cur = db.cursor()
        sql = "select * from yginfo"
        cur.execute(sql)
        data = cur.fetchall()    # 获取查询到的数据，是以二维元组的形式存储的，所以读取需要使用data[i][j] 下标定位
        print(data)
        if data:
            self.tableWidget.setRowCount(0)
            self.tableWidget.insertRow(0)
            for row, form in enumerate(data):
                for column, item in enumerate(form):
                    self.tableWidget.setItem(row, column, QTableWidgetItem(str(item)))
                    column += 1
                row_position = self.tableWidget.rowCount()
                self.tableWidget.insertRow(row_position)

    def chaxun_yg(self):
        db = con_mysql.get_conn()
        cur = db.cursor()
        sql = "select * from yginfo where id=%s"
        id = self.idbutton.text()
        cur.execute(sql, (id))
        data = cur.fetchone()
        if data:
            self.add_id_2.setText(str(data[0]))
            self.namebutton.setText(data[1])
            self.sex_button.setText(data[2])
            self.age_button.setText(data[3])
            self.bm_button.setText(str(data[4]))

    def ssinfo(self):
        db = con_mysql.get_conn()
        cur = db.cursor()
        sql = "select * from yginfo where id=%s"
        id = self.id_2.text()

        cur.execute(sql, (id))
        data = cur.fetchone()
        if data:
            self.groupBox_6.setEnabled(True)
            self.addid_3.setText(str(data[0]))
            self.addname3.setText(data[1])
            self.sex_3.setText(data[2])
            self.age_3.setText(data[3])
            self.bumen.setText(str(data[4]))

    def xginfo(self):
        id = self.id_2.text()
        a=self.addid_3.text()
        b=self.addname3.text()
        c=self.sex_3.text()
        d=self.age_3.text()
        e=self.bumen.text()
        db = con_mysql.get_conn()
        cur = db.cursor()
        sql = "update yginfo set id=%%s,name=%%s,sex=%%s,age=%%s,bm_id=%%s where id=%%s"
        cur.execute(sql, (a,b,c,d,e,id))
        db.commit()
        con_mysql.close_conn(cur,db)

    def deleteinfo(self):
        id = self.id_2.text()
        warning = QMessageBox.warning(self, "删除员工信息", "您确定删除吗？", QMessageBox.Yes | QMessageBox.No)
        if warning == QMessageBox.Yes:
            db = con_mysql.get_conn()
            cur = db.cursor()
            sql = "delete from yginfo where id=%s"
            cur.execute(sql, (id))
            db.commit()
            con_mysql.close_conn(cur, db)

    def add_yggz(self):
        # 数据库操作流程
        # 1.获取连接
        db = con_mysql.get_conn()
        # 2.获取游标
        cur = db.cursor()
        # 3.SQL语句
        sql = "insert into yggz(id,yggz) values(%s,%s)"
        a = self.ad_id_4.text()  # 实现从指定界面输入框获取指定输入信息
        b = self.add_gz_7.text()
        cur.execute(sql, (a, b))
        # 5.提交
        db.commit()
        # 6.关闭资源
        con_mysql.close_conn(cur, db)

    def show_yggz(self):
        db = con_mysql.get_conn()
        cur = db.cursor()
        sql = "select * from yggz"
        cur.execute(sql)
        data = cur.fetchall()  # 获取查询到的数据，是以二维元组的形式存储的，所以读取需要使用data[i][j] 下标定位
        print(data)
        if data:
            self.tableWidget_3.setRowCount(0)
            self.tableWidget_3.insertRow(0)
            for row, form in enumerate(data):
                for column, item in enumerate(form):
                    self.tableWidget_3.setItem(row, column, QTableWidgetItem(str(item)))
                    column += 1
                row_position = self.tableWidget_3.rowCount()
                self.tableWidget_3.insertRow(row_position)


    def ssgz(self):
        db = con_mysql.get_conn()
        cur = db.cursor()
        sql = "select * from yggz where id=%s"
        id = self.id_3.text()
        cur.execute(sql, (id))
        data = cur.fetchone()
        if data:
            self.groupBox_15.setEnabled(True)
            self.addid_4.setText(str(data[0]))
            self.addname3_2.setText(str(data[1]))

    def xg_gzinfo(self):
        id = self.id_3.text()
        a = self.addid_4.text()
        b = self.addname3_2.text()
        db = con_mysql.get_conn()
        cur = db.cursor()
        sql = "update yggz set id=%s,yggz=%s where id=%s"
        cur.execute(sql, (a, b, id))
        db.commit()
        con_mysql.close_conn(cur, db)

    def delete_gz_info(self):
        id = self.id_3.text()
        warning = QMessageBox.warning(self, "删除工资信息", "您确定删除吗？", QMessageBox.Yes | QMessageBox.No)
        if warning == QMessageBox.Yes:
            db = con_mysql.get_conn()
            cur = db.cursor()
            sql = "delete from yggz where id=%s"
            cur.execute(sql, (id))
            db.commit()
            con_mysql.close_conn(cur, db)

    def add_bm(self):
        # 数据库操作流程
        # 1.获取连接
        db = con_mysql.get_conn()
        # 2.获取游标
        cur = db.cursor()
        # 3.SQL语句
        sql = "insert into bm(bm_id,bm_name) values(%s,%s)"
        a = self.bmadd.text()  # 实现从指定界面输入框获取指定输入信息
        b = self.bmnmae.text()
        cur.execute(sql, (a, b))
        # 5.提交
        db.commit()
        # 6.关闭资源
        con_mysql.close_conn(cur, db)

    def show_bm(self):
        db = con_mysql.get_conn()
        cur = db.cursor()
        sql = "select * from bm"
        cur.execute(sql)
        data = cur.fetchall()  # 获取查询到的数据，是以二维元组的形式存储的，所以读取需要使用data[i][j] 下标定位
        print(data)
        if data:
            self.tableWidget_2.setRowCount(0)
            self.tableWidget_2.insertRow(0)
            for row, form in enumerate(data):
                for column, item in enumerate(form):
                    self.tableWidget_2.setItem(row, column, QTableWidgetItem(str(item)))
                    column += 1
                row_position = self.tableWidget_2.rowCount()
                self.tableWidget_2.insertRow(row_position)

    def chaxun_bm(self):
        db = con_mysql.get_conn()
        cur = db.cursor()
        sql = "select * from bm where bm_id=%s"
        id = self.bmid6.text()
        cur.execute(sql, (id))
        data = cur.fetchone()
        if data:
            self.add_bmid_5.setText(str(data[0]))
            self.addbm_name.setText(str(data[1]))

    def ssbm(self):
        db = con_mysql.get_conn()
        cur = db.cursor()
        sql = "select * from bm where bm_id=%s"
        id = self.bm_new_id.text()
        cur.execute(sql, (id))
        data = cur.fetchone()
        if data:
            self.groupBox_10.setEnabled(True)
            self.add_bm_id_6.setText(str(data[0]))
            self.bmname_9.setText(str(data[1]))

    def xg_bminfo(self):
        id = self.bm_new_id.text()
        a = self.add_bm_id_6.text()
        b = self.bmname_9.text()
        db = con_mysql.get_conn()
        cur = db.cursor()
        sql = "update bm set bm_id=%s,bm_name=%s where bm_id=%s"
        cur.execute(sql, (a, b, id))
        db.commit()
        con_mysql.close_conn(cur, db)

    def delete_bm_info(self):
        id = self.bm_new_id.text()
        warning = QMessageBox.warning(self, "删除部门信息", "您确定删除吗？", QMessageBox.Yes | QMessageBox.No)
        if warning == QMessageBox.Yes:
            db = con_mysql.get_conn()
            cur = db.cursor()
            sql = "delete from bm where bm_id=%s"
            cur.execute(sql, (id))
            db.commit()
            con_mysql.close_conn(cur, db)

    def user_login(self):
        db = con_mysql.get_conn()
        cur = db.cursor()
        sql = "select * from user where name=%s and pwd=%s"
        a = self.user_name.text()
        b = self.user_pwd.text()
        cur.execute(sql, (a, b))
        data = cur.fetchone()
        if data:
            self.groupBox1.setEnabled(True)
            self.user_name1.setText(data[1])
            self.user_pwd1.setText(data[2])

    def xg_user(self):
        name = self.user_name.text()
        name1= self.user_name1.text()
        pwd = self.user_pwd1.text()
        pwd1 = self.user_pwd2.text()
        if pwd == pwd1:
            db = con_mysql.get_conn()
            cur = db.cursor()
            sql = "update user set name=%s,pwd=%s where name=%s"
            cur.execute(sql, (name1, pwd,name))
            db.commit()
            con_mysql.close_conn(cur, db)





#####################################################################################################################
def main():
    app = QApplication([])     # 创建应用程序 创建一个QApplication，也就是你要开发的软件app
    window = Ygdl()         # 创建主窗口  创建一个MainAPP，用来装载你需要的各种组件、控件
    window.show()              # 显示窗口 执行QMainWindow的show()方法，显示这个QMainWindow
    app.exec_()                # 使用exit()或者点击关闭按钮退出QApplication

if __name__ == '__main__':

    main()


